import json
import os
import pprint
import time

import jsonpath
import openpyxl
import xlrd
from openpyxl.styles import PatternFill, Border, Side
import pandas as pd


"""
会议决定，改造excel用例，改成通用格式
用例参数、预期结果用json字符串填写
"""
class Excel_Obj:
    def __init__(self,file,sheet):
        self.file = file
        self.sheet = sheet
        self.work_book = xlrd.open_workbook(self.file,formatting_info=True)
        self.work_sheet = self.work_book.sheet_by_name(self.sheet)
        self.max_col = self.work_sheet.ncols

    def get_excel_data(self,header,row):
        """
        根据excel表头获取数据，如header="用例标题"，则获取这一列的数据，可再根据row定位
        :param header: 表头
        :param row: 行
        """
        # 第一行--表头数据
        header_list = [] # 表头形成的列表
        for one in range(0,self.max_col):
            header_list.append(self.work_sheet.cell_value(0,one))
        if header in header_list:
            col_index = header_list.index(header) # header的索引值，与列索引一致
            data = self.work_sheet.cell_value(row, col_index)  # 取出header列的值
            return data

    def get_format_data(self,startrow,endrow):
        """
        根据get_excel_data 获取出功能点、用例标题、用例参数、预期结果组成的列表套元组的对象
        :param startrow: 起始行号
        :param endrow: 截止行号
        :return: 单元格值，结果是[(),()]类型
        """
        all_data = []
        for i in range(startrow,endrow+1):
            function_point = self.get_excel_data('功能点',i)
            case_title = self.get_excel_data('用例标题',i)
            # 如果用例参数、预期结果中有 '{' 则认为是json字符串，将其转为dict备用，否则就是普通的字符串，不作处理
            case_param = json.loads(self.get_excel_data('用例参数',i)) if '{' in self.get_excel_data('用例参数',i) else self.get_excel_data('用例参数',i)
            expect_result = json.loads(self.get_excel_data('预期结果',i)) if '{' in self.get_excel_data('预期结果',i) else self.get_excel_data('预期结果',i)
            all_data.append((function_point,case_title,case_param,expect_result))
        return all_data

def modifycolor_excel_openpyxl(excelName,sheetName,Row,Col,Color):
    '''
    :param excelName: 测试用例xlsx文件路径
    :param saveExcelName: 测试用例执行后的保存路径
    :param sheetName: 测试用例所在的sheet页签名称
    :param Row: 写入数据的行号
    :param Col: 写入数据的列号
    :param Color: 颜色代码
    '''
    excelDir = '../data/{}'.format(excelName)
    red_fill = PatternFill("solid", fgColor="FF0000")   # 红色填充
    green_fill = PatternFill("solid", fgColor="90EE90") # 绿色填充
    wb = openpyxl.load_workbook(excelDir)
    ws = wb[sheetName]
    fill = PatternFill("solid", fgColor=Color)
    # 给单元格修改颜色
    ws.cell(Row,Col).fill = fill
    wb.save(excelDir)


class ExportReport:
    def __init__(self,report_path):
        """
        初始化返回allure报告的json文件列表，后缀为-result.json的文件，都是结果数据文件
        :param report_path: 生成allure的json文件路径
        """
        self.result_list = []
        for root, ds,fs in os.walk(report_path):
            for f in fs:
                if f.endswith('-result.json'):
                    fullname = os.path.join(root, f)
                    self.result_list.append(fullname)

    def report(self,excelpath):
        wb = openpyxl.load_workbook(excelpath)
        ws = wb.active
        # fill = PatternFill("solid", fgColor="FF0000")
        row = 2 # 初始行，去掉表头行
        for result in self.result_list:
            with open(result,'r',encoding='utf-8') as f:
                result_data = json.loads(f.read()) # 所有数据字典化
                case_title = result_data['name'] # 用例标题
                status = result_data['status'] # 是否通过状态
                action_timeStamp = result_data['start'] # 执行时间
                # print(action_timeStamp)
                timeArray = time.localtime(float(action_timeStamp / 1000))
                action_time = time.strftime("%Y-%m-%d %H:%M:%S", timeArray)
                status = '是' if status == 'passed' else '否'
                labels = result_data['labels']
                function_point = jsonpath.jsonpath(labels,'?(@.name=="story")')[0]['value']
                function_module = jsonpath.jsonpath(labels,'?(@.name=="feature")')[0]['value']
                ws.cell(row,1).value,ws.cell(row,1).border = function_module, Border(left=Side(style='thin'), bottom=Side(style='thin'),
                                                                   right=Side(style='thin'), top=Side(style='thin'))
                ws.cell(row,2).value,ws.cell(row,2).border  = function_point, Border(left=Side(style='thin'), bottom=Side(style='thin'),
                                                                   right=Side(style='thin'), top=Side(style='thin'))
                ws.cell(row,3).value,ws.cell(row,3).border  = case_title, Border(left=Side(style='thin'), bottom=Side(style='thin'),
                                                                   right=Side(style='thin'), top=Side(style='thin'))
                ws.cell(row,4).value,ws.cell(row,4).border  = action_time, Border(left=Side(style='thin'), bottom=Side(style='thin'),
                                                                   right=Side(style='thin'), top=Side(style='thin'))
                ws.cell(row, 5).value, ws.cell(row, 5).border = status, Border(left=Side(style='thin'),bottom=Side(style='thin'),
                                                                                    right=Side(style='thin'),
                                                                                    top=Side(style='thin'))

                # # 给单元格修改颜色
                # if status == '否':
                #     ws.cell(row, 5).fill = fill
            row = row + 1

        wb.save(excelpath)


    # 这个方法会改掉格式
    def sort_excel(self,excelpath,sheet_name,col_name):
        # 读取上一步保存的Excel文件
        df = pd.read_excel(excelpath, sheet_name=sheet_name)
        df_value = df.sort_values(by=[col_name], ascending=False)
        # 保存文件
        writer = pd.ExcelWriter(excelpath)
        df_value.to_excel(writer, sheet_name=sheet_name, index=False)
        writer.close()

    def fiflter(self):
        workbook = openpyxl.load_workbook(filename="../outFiles/report/allure-report/data/导出模板.xlsx")
        sheet = workbook.active
        sheet.auto_filter.ref = sheet["A1:E1"]
        workbook.save(filename="../outFiles/report/allure-report/data/导出模板.xlsx")

def sort_excel_new(excelpath,sheet_name):

    # 打开Excel文件
    wb = openpyxl.load_workbook(excelpath)

    # 选择工作表
    ws = wb[sheet_name]

    # 获取某一列的所有值
    column = []
    for row in ws.iter_rows(min_row=1, min_col=4, max_col=4, values_only=True):
        column.append(row[0])

    # 对这一列进行排序
    column.sort()

    # 把排好的列写回Excel中
    for i, value in enumerate(column, start=1):
        ws.cell(row=i, column=4, value=value)

    # 保存文件，保留格式
    wb.save(excelpath)

def sort_excel_v2(excelpath):
    # Load the Excel file
    wb = openpyxl.load_workbook(excelpath)
    ws = wb.active
    fill = PatternFill("solid", fgColor="FF0000")
    # Get all the data from the column you want to sort
    data = []
    for row in ws.iter_rows(min_row=2, max_col=ws.max_column, values_only=True):
        data.append(row)
    # Sort the data
    data = sorted(data, key=lambda x: x[3]) # 第4列数据进行排序

    # Write the sorted data back to the Excel file, preserving formatting
    for i, row in enumerate(data, start=2):
        for j, value in enumerate(row):
            ws.cell(row=i, column=j + 1, value=value)
            if j+1 == 5: # 给第5列填充颜色，否就是红色
                if ws.cell(row=i,column=5).value == '否':
                    ws.cell(i,5).fill = fill

    # Save the changes
    wb.save(excelpath)


if __name__ == '__main__':
    # excel = Excel_Obj('../testData/XJGC_gysjg0.xls','工程量模块') # 实例化打开“XJGC_gysjg0.xls”文件的“工程量模块”sheet页
    # for i in range(1,3):
    #     print(excel.getform_data('电压等级',i))
    # print(excel.get_excel_data('预期结果',2)) # 打印“预期结果”列的第2行数据
    # print( excel.get_format_data(1, 1)) # 打印出第1行到第1行的所有数据
    # professional, projectname, resourcetype, keyword = excel.get_format_data(1, 1)[0][2].values()
    # print(excel.get_format_data(1, 1)[0][2])
    # print(projectname)

    # 遍历文件夹文件
    f = ExportReport('../outFiles/report') # allure的json文件存放位置
    f.report('../outFiles/report/allure-report/data/导出模板.xlsx') # 修改excel并填写数据

    sort_excel_v2('../outFiles/report/allure-report/data/导出模板.xlsx') # 按时间排序excel

    # 修改生成allure报告的js文件，将导出按钮内容改成我们的导出模板.xlsx
    with open('../outFiles/report/allure-report/app.js','r',encoding='utf8') as f:
        c = f.read()
    with open('../outFiles/report/allure-report/app.js','w',encoding='utf8') as f2:
        a = c.replace("'+s(e.lambda(null!=t?t.csvUrl:t,t))+'","data/导出模板.xlsx")
        f2.write(a)

    # 执行完这个py文件后，继续执行allure报告生成的语句，该语句在run.py中